package com.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.log4j.Logger;

import com.dao.DataOperations;
import com.dbtools.Dispose;
import com.exception.SelectException;
import com.info.UserInfo;
import com.util.CommonTools;
import com.util.SessionUtils;
/**
 * @author luanzhe 2006-1-19
 */
public class GrantVerifyDAO extends DataOperations {
	Logger logger = Logger.getLogger(GrantVerifyDAO.class);

	public GrantVerifyDAO() {
		
	}

	public GrantVerifyDAO(Connection connection) {
		super(connection);
	}

	/**
	 * ���û���Ϣ
	 * 
	 * @param username
	 * @return
	 * @throws SelectException
	 */
	public UserInfo queryUserInfoByUsername(String username) {
		UserInfo info = new UserInfo();
		StringBuffer sql = new StringBuffer();
		sql
				.append("SELECT a.userid, a.code, a.NAME, a.PASSWORD, a.orgid, a.managorgid, a.tel,");
		sql.append("       a.mobil, a.notes, a.isdel, b.TYPE ");
		sql.append("FROM   t_s_user a,t_s_org b ");
		sql.append("WHERE  a.code = '" + username + "' AND a.orgid = b.orgid");
		ResultSet rest = null;
		try {
			
			
			System.out.println("sql is "+sql);
			
			
			rest = this.executeQuery(sql.toString());
			while (rest.next()) {
				info.setUserId(CommonTools
						.null2String(rest.getString("USERID")));
				info.setCode(CommonTools.null2String(rest.getString("CODE")));
				info.setName(CommonTools.null2String(rest.getString("NAME")));
				info.setPassword(CommonTools.null2String(rest
						.getString("PASSWORD")));
				info.setOrgId(CommonTools.null2String(rest.getString("ORGID")));
				info.setManagOrgId(CommonTools.null2String(rest
						.getString("MANAGORGID")));
				info.setTel(CommonTools.null2String(rest.getString("TEL")));
				info.setMobil(CommonTools.null2String(rest.getString("MOBIL")));
				info.setNotes(CommonTools.null2String(rest.getString("NOTES")));
				info.setIsDel(CommonTools.null2String(rest.getString("ISDEL")));
				info.setTYPE(CommonTools.null2String((rest.getString("TYPE"))));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest);
		}
		return info;
	}

	/**
	 * ��λ��Ϣ��ѯ
	 * 
	 * @param userId
	 * @return
	 * @throws SelectException
	 */
	public List queryOrg(String userId) throws SelectException {
		List list = new ArrayList();
		StringBuffer sql = new StringBuffer();
		sql
				.append(
						"select ORGID,CODE,NAME from T_S_ORG where orgid in (SELECT orgid FROM T_S_USER WHERE userid = '")
				.append(userId).append("' and isdel <> '1') ");
		ResultSet rest = null;
		Map map = null;
		try {
			rest = super.executeQuery(sql.toString());
			while (rest.next()) {
				map = new HashMap();
				map.put("ORG_ID", rest.getString("ORGID"));
				map.put("ORG_CODE", rest.getString("CODE"));
				map.put("ORG_NAME", rest.getString("NAME"));
				list.add(map);
			}
		} catch (SQLException e) {
			throw new SelectException(e.getMessage() + sql);
		} finally {
			Dispose.dispose(rest);
		}
		return list;
	}

	/**
	 * ��λ��Ϣ��ѯ
	 * 
	 * @param userId
	 * @return
	 * @throws SelectException
	 */
	public List queryManagOrg(String userId) throws SelectException {
		List list = new ArrayList();
		StringBuffer sql = new StringBuffer();
		sql.append("select ORGID,CODE,NAME from T_S_ORG");
//		sql
//				.append(
//						"select ORGID,CODE,NAME from T_S_ORG where instr((SELECT ','||managOrgid||',' FROM T_S_USER WHERE userid = '")
//				.append(userId).append(
//						"' and isdel <> '1'), ','||orgid||',') >0 ");
		ResultSet rest = null;
		Map map = null;
		try {
			rest = super.executeQuery(sql.toString());
			while (rest.next()) {
				map = new HashMap();
				map.put("ORG_ID", rest.getString("ORGID"));
				map.put("ORG_CODE", rest.getString("CODE"));
				map.put("ORG_NAME", rest.getString("NAME"));
				list.add(map);
			}
		} catch (SQLException e) {
			throw new SelectException(e.getMessage() + sql);
		} finally {
			Dispose.dispose(rest);
		}
		return list;
	}

	/**
	 * ��ɫ��Ϣ��ѯ
	 * 
	 * @param userId
	 * @param orgId
	 * @return
	 * @throws SelectException
	 */
	public List queryRoles(String userId, String orgId) throws SelectException {
		List list = new ArrayList();
		StringBuffer sql = new StringBuffer();
		sql
				.append("select ROLEID, CODE, NAME from T_S_ROLE where roleid in (select roleid from T_S_USER_ROLE where userid='"
						+ userId + "')");
		ResultSet rest = null;
		Map map = null;
		try {
			rest = super.executeQuery(sql.toString());
			while (rest.next()) {
				map = new HashMap();
				map.put("ROLE_ID", rest.getString("ROLEID"));
				map.put("ROLE_CODE", rest.getString("CODE"));
				map.put("ROLE_NAME", rest.getString("NAME"));
				list.add(map);
			}
		} catch (SQLException e) {
			throw new SelectException(e.getMessage() + sql);
		} finally {
			Dispose.dispose(rest);
		}
		return list;
	}

	/**
	 * ���ܵ���Ϣ��ѯ
	 * 
	 * @param root
	 *            ��
	 * @param maxLevel
	 *            ������
	 * @param strFunctions
	 *            ���ܵ�ID��
	 * @return ���㼶���ֵĹ��ܵ���Ϣ����
	 * @throws SelectException
	 */
	public List queryFunctions(HttpServletRequest request) throws SelectException {
		String roleId = SessionUtils.getRoleId(request);
		String locale = SessionUtils.getLocale(request);
		List ZSY01pList = new ArrayList();
		String strFunctions = CommonTools.null2String(roleId).replaceAll(",",
				"','");
		StringBuffer subSql = new StringBuffer();
		subSql.append("SELECT DISTINCT a.functionid, code, b.NAME, parentid, url, notes,xh");
		subSql.append(" FROM t_s_function a,t_s_function_language b");
		subSql.append(" where a.functionid=b.functionid and" +
					  " a.functionid in (select functionid from T_S_ROLE_FUNCTION where roleid in('"
						+ strFunctions + "')) ");
		// subSql.append(" CONNECT BY PRIOR functionid = parentid START WITH
		// functionid = ANY('"+strFunctions+"') ");
		// subSql.append(" ORDER SIBLINGS BY code ");
		subSql.append(" and b.language='"+locale+"'");
		subSql.append(" ORDER BY parentid,xh");
		System.out.println("subSql = "+subSql.toString());
		logger.info(subSql.toString());
		Map map = null;
		ResultSet rest = null;
		try {
			rest = this.executeQuery(subSql.toString());
			while (rest.next()) {
				map = new HashMap();
				map.put("FUNCTIONID", CommonTools.null2String(rest
						.getString("FUNCTIONID")));
				map
						.put("CODE", CommonTools.null2String(rest
								.getString("CODE")));
				map
						.put("NAME", CommonTools.null2String(rest
								.getString("NAME")));
				map.put("PARENTID", CommonTools.null2String(rest
						.getString("PARENTID")));
				map.put("URL", CommonTools.null2String(rest.getString("URL")));
				map.put("NOTES", CommonTools.null2String(rest
						.getString("NOTES")));
				ZSY01pList.add(map);
			}
		} catch (SQLException e) {
			throw new SelectException(e.getMessage());
		} finally {
			Dispose.dispose(rest);
		}
		return ZSY01pList;
	}

	public Map querySystems() throws SelectException {
		Map reMap = new HashMap();
		StringBuffer sql = new StringBuffer();
		sql
				.append("select ID,GNDBM,GNDMC,SJGNDID,GNDLX,URL,QXZ,BZ from COMMON.T_COMMON_D_FUNCTION where sjgndid = '****'");
		Map map = null;
		ResultSet rest = null;
		try {
			rest = super.executeQuery(sql.toString());
			while (rest.next()) {
				map = new HashMap();
				map.put("ID", CommonTools.null2String(rest.getString("ID")));
				map.put("GNDBM", CommonTools.null2String(rest
						.getString("GNDBM")));
				map.put("GNDMC", CommonTools.null2String(rest
						.getString("GNDMC")));
				map.put("SJGNDID", CommonTools.null2String(rest
						.getString("SJGNDID")));
				map.put("GNDLX", CommonTools.null2String(rest
						.getString("GNDLX")));
				map.put("URL", CommonTools.null2String(rest.getString("URL")));
				map.put("QXZ", CommonTools.null2String(rest.getString("QXZ")));
				map.put("BZ", CommonTools.null2String(rest.getString("BZ")));
				reMap.put(CommonTools.null2String(rest.getString("ID")), map);
			}
		} catch (SQLException e) {
			throw new SelectException(e.getMessage());
		} finally {
			Dispose.dispose(rest);
		}

		return reMap;
	}

	public String getUserType(String orgId) throws SelectException {
		String returnValue = "";
		StringBuffer sql = new StringBuffer();
//		sql
//				.append("select orgid from t_s_org where parentid = '0' start with orgid = '"
//						+ orgId + "' connect by prior parentid = orgid");
		sql.append("select orgid from t_s_org where parentid = '0'");
		ResultSet rest = null;
		try {
			rest = super.executeQuery(sql.toString());
			while (rest.next()) {
				returnValue = CommonTools.null2String(rest.getString("ORGID"));
			}
		} catch (SQLException e) {
			throw new SelectException(e.getMessage());
		} finally {
			Dispose.dispose(rest);
		}
		return returnValue;
	}

	/**
	 * ��֤�û��Ƿ����
	 */
	public boolean userVerify(String username, String password)
			throws SelectException {
		boolean flag = false;
		String sql = "SELECT COUNT(*) FROM T_S_USER WHERE code = '"
				+ CommonTools.convertorQuote(username) + "' AND password = '"
				+ CommonTools.convertorQuote(password) + "'";
		ResultSet rest = null;
		int userCount = 0;
		try {
			
			System.out.println("sql is "+sql);
			
			rest = this.executeQuery(sql);
			while (rest.next()) {
				userCount = rest.getInt(1);
				if (userCount >= 1) {
					flag = true;
				}
			}
		} catch (SQLException e) {
			throw new SelectException(e.getMessage());
		} finally {
			Dispose.dispose(rest);
		}
		return flag;
	}

	/**
	 * ��֤�û�IP�Ƿ������Ʒ�Χ��
	 */
	public boolean userVerifyIP(String username, String ip)
			throws SelectException {
		boolean flag = false;
		String sql = "SELECT IP FROM T_S_USER WHERE code = '"
				+ CommonTools.convertorQuote(username) + "' ";
		ResultSet rest = null;
		String tempIp = "";
		try {
			rest = this.executeQuery(sql);
			while (rest.next()) {
				tempIp = CommonTools.null2String(rest.getString("IP"));
			}
			flag = this.checkIp(tempIp, ip);
		} catch (SQLException e) {
			throw new SelectException(e.getMessage());
		} finally {
			Dispose.dispose(rest);
		}
		return flag;
	}

	private boolean checkIp(String ip1, String ip2) {
		boolean flag = false;
		if (CommonTools.isNullString(ip1)) {
			return true;
		}

		try {
			String ip = ip1.substring(0, ip1.lastIndexOf("."));
			int startIndex = Integer.parseInt(ip.substring(
					ip.lastIndexOf(".") + 1, ip.length()));
			int endIndex = Integer.parseInt(ip1.substring(
					ip1.lastIndexOf(".") + 1, ip1.length()));

			if (ip.substring(0, ip.lastIndexOf(".")).equals(
					ip2.substring(0, ip2.lastIndexOf(".")))) {
				int key = Integer.parseInt(ip2.substring(
						ip2.lastIndexOf(".") + 1, ip2.length()));
				if (key <= endIndex && key >= startIndex) {
					flag = true;
				} else {
					flag = false;
				}
			} else {
				flag = false;
			}
		} catch (Exception e) {
			flag = false;
		}
		return flag;
	}
}
